package com.easytopit.demo.dao;

import java.util.List;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Result;
import org.apache.ibatis.annotations.Results;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.type.JdbcType;

import com.easytopit.demo.domain.Comment;
import com.easytopit.demo.domain.Movie;
import com.easytopit.demo.domain.Score;

public interface MovieMapper {
	
	@Select({"select name from movie where name = #{name,jdbcType=VARCHAR}"})
	String checkName(@Param("name")String name);
	
	@Insert({"insert into movie (name,cover,profile,on_time,off_time,country,director,length)",
		"values (#{name,jdbcType=VARCHAR},#{cover,jdbcType=VARCHAR},#{profile,jdbcType=VARCHAR},"
		+ "#{onTime,jdbcType=VARCHAR},#{offTime,jdbcType=VARCHAR},#{country,jdbcType=VARCHAR},"
		+ "#{director,jdbcType=VARCHAR},#{length,jdbcType=VARCHAR})"})
	void addMovie(Movie movie);
	
	@Select({"select version from movie_version where movie_name = #{name,jdbcType=VARCHAR}"})
	String checkVersion(@Param("name")String name);
	
	@Insert({"insert into movie_version (movie_name,version,price)",
		"values (#{name,jdbcType=VARCHAR},#{version,jdbcType=VARCHAR},#{price,jdbcType=DOUBLE})"})
	void addVersion(@Param("name")String name,@Param("version")String version,@Param("price")double price);
	
	@Select({"select type from movie_type where movie_name = #{name,jdbcType=VARCHAR}"})
	String checkType(@Param("name")String name);
	
	@Insert({"insert into movie_type (movie_name,type)",
		"values (#{name,jdbcType=VARCHAR},#{type,jdbcType=VARCHAR})"})
	void addType(@Param("name")String name,@Param("type")String type);
	
	@Select({"select actor_name from movie_actor where movie_name = #{name,jdbcType=VARCHAR}"})
	String checkActor(@Param("name")String name);
	
	@Insert({"insert into movie_actor (movie_name,actor_name)",
		"values (#{name,jdbcType=VARCHAR},#{actor,jdbcType=VARCHAR})"})
	void addActor(@Param("name")String name,@Param("actor")String actor);
	
	@Insert({"insert into movie_photo (movie_name,photo)",
		"values (#{name,jdbcType=VARCHAR},#{photo,jdbcType=VARCHAR})"})
	void addPhoto(@Param("name")String name,@Param("photo")String photo);
	
	@Select({"select name from cinema where city = #{city,jdbcType=VARCHAR}"})
	String checkCinema(@Param("city")String city,@Param("name")String name);
	
	@Insert({"insert into cinema (city,name)",
		"values (#{city,jdbcType=VARCHAR},#{name,jdbcType=VARCHAR})"})
	void addCinema(@Param("city")String city,@Param("name")String name);
	
	@Select({"select name from hall where cinema_name = #{cinemaName,jdbcType=VARCHAR}"})
	String checkHall(@Param("cinemaName")String cinemaName,@Param("name")String name);
	
	@Insert({"insert into hall (cinema_name,name)",
		"values (#{cinemaName,jdbcType=VARCHAR},#{name,jdbcType=VARCHAR})"})
	void addHall(@Param("cinemaName")String cinemaName,@Param("name")String name);
	
	@Select({"select * from movie"})
	@Results({
        @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
        @Result(column="cover", property="cover", jdbcType=JdbcType.VARCHAR),
        @Result(column="profile", property="profile", jdbcType=JdbcType.VARCHAR),
        @Result(column="status", property="status", jdbcType=JdbcType.VARCHAR),
        @Result(column="on_time", property="onTime", jdbcType=JdbcType.VARCHAR),
        @Result(column="country", property="country", jdbcType=JdbcType.VARCHAR),
        @Result(column="director", property="director", jdbcType=JdbcType.VARCHAR),
        @Result(column="length", property="length", jdbcType=JdbcType.VARCHAR)
    })
	List<Movie> movieList();
	
	@Select({"select * from movie where name like #{name,jdbcType=VARCHAR}"})
	@Results({
        @Result(column="name", property="name", jdbcType=JdbcType.VARCHAR),
        @Result(column="cover", property="cover", jdbcType=JdbcType.VARCHAR),
        @Result(column="profile", property="profile", jdbcType=JdbcType.VARCHAR),
        @Result(column="status", property="status", jdbcType=JdbcType.VARCHAR),
        @Result(column="on_time", property="onTime", jdbcType=JdbcType.TIMESTAMP),
        @Result(column="country", property="country", jdbcType=JdbcType.VARCHAR),
        @Result(column="director", property="director", jdbcType=JdbcType.VARCHAR),
        @Result(column="length", property="length", jdbcType=JdbcType.INTEGER)
    })
	List<Movie> queryMovieByName(@Param("name")String name);
	
	@Select({"select name from movie where status = #{status,jdbcType=VARCHAR}"})
	List<String> queryMovieByStatus(@Param("status")String status);
	
	@Select({"select movie_name from movie_type where type = #{type,jdbcType=VARCHAR}"})
	List<String> queryMovieByType(@Param("type")String type);
	
	@Select({"select movie_name from movie_actor where actor_name = #{actor,jdbcType=VARCHAR}"})
	List<String> queryMovieByActor(@Param("actor")String actor);
	
	@Select({"select director from movie where director = #{director,jdbcType=VARCHAR}"})
	List<String> queryMovieByDirector(@Param("director")String director);
	
	@Select({"select country from movie where country = #{country,jdbcType=VARCHAR}"})
	List<String> queryMovieByCountry(@Param("country")String country);
	
	@Select({"select type from movie_type where movie_name = #{name,jdbcType=VARCHAR}"})
	List<String> queryTypeByName(@Param("name")String name);
	
	@Select({"select actor_name from movie_actor where movie_name = #{name,jdbcType=VARCHAR}"})
	List<String> queryActorByName(@Param("name")String name);
	
	@Select({"select photo from movie_photo where movie_name = #{name,jdbcType=VARCHAR}"})
	List<String> queryPhotoByName(@Param("name")String name);
	
	@Select({"select * from movie_comment where movie_name = #{name,jdbcType=VARCHAR}"})
	@Results({
        @Result(column="user_name", property="userName", jdbcType=JdbcType.VARCHAR),
        @Result(column="movie_name", property="movieName", jdbcType=JdbcType.VARCHAR),
        @Result(column="comment", property="comment", jdbcType=JdbcType.VARCHAR)
    })
	List<Comment> queryCommentByName(@Param("name")String name);
	
	@Select({"select * from movie_comment where user_name = #{name,jdbcType=VARCHAR}"})
	@Results({
        @Result(column="user_name", property="userName", jdbcType=JdbcType.VARCHAR),
        @Result(column="movie_name", property="movieName", jdbcType=JdbcType.VARCHAR),
        @Result(column="comment", property="comment", jdbcType=JdbcType.VARCHAR)
    })
	List<Comment> queryCommentByUserName(@Param("name")String name);

	@Select({"select * from movie_score where movie_name = #{name,jdbcType=VARCHAR}"})
	@Results({
        @Result(column="user_name", property="userName", jdbcType=JdbcType.VARCHAR),
        @Result(column="movie_name", property="movieName", jdbcType=JdbcType.VARCHAR),
        @Result(column="score", property="score", jdbcType=JdbcType.DOUBLE)
    })
	List<Score> queryScoreByName(@Param("name")String name);
	
	@Select({"select avg(score) from movie_score where movie_name  = #{name,jdbcType=VARCHAR}"})
	Double avg(@Param("name")String name);
	
	@Select({"select price from movie_version where movie_name = #{movie,jdbcType=VARCHAR} and version = #{version,jdbcType=VARCHAR}"})
	Double queryPriceByVersion(@Param("movie")String movie,@Param("version")String version);
}